package com.bw.dao;

import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select sum(amount) `value` from order_info where create_time >='2023-01-06 00:00:00' and create_time <='2023-01-06 23:59:59'")
    double getList1();

    @Select("call sp1();")
    List<Sugar1> getList2();

    @Select("select a.*,RANK() over(PARTITION by a.name ORDER BY a.distance) rak from (select o.org_name name,\n" +
            "count(actual_distance) distance,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) `time`,\n" +
            "(sum(actual_distance)/count(org_name)) avgDistance,\n" +
            "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(org_name)) avgNum\n" +
            "from transport_task t \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id \n" +
            "LEFT JOIN base_organ o ON l.org_id=o.id\n" +
            "GROUP BY o.org_name) a")
    List<Sugar2> getList3();

    @Select("select i1.name,count(d.sorter_emp_id) `value` from transport_task_detail d \n" +
            "LEFT JOIN transport_task t ON d.transport_task_id=t.id \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id \n" +
            "LEFT JOIN base_organ o ON l.org_id=o.id \n" +
            "LEFT JOIN base_region_info i ON o.region_id=i.id\n" +
            "LEFT JOIN base_region_info i1 ON i.parent_id=i1.id\n" +
            "GROUP BY i1.name ")
    List<Sugar1> getList4();
}
